using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for CustomBookingDAO
/// </summary>
public class CustomBookingDAO:DBAccess
{
	#region sql Commands   
    string SQL_SELECT_ALL = "select * from CustomBooking";
    string SQL_SELECT_BY_ID = "select * from CustomBooking where ID=@id";
    string SQL_SELECT_BY_BID = "select * from CustomBooking where BookingID=@bId";
    string SQL_INSERT = "insert into CustomBooking(FacilityID,GuestNumber,DeparturePlaceID,DepartureDate,ArrivalPlaceID,Price,Note, BookingID)values(@FacilityID,@GuestNumber,@DeparturePlaceID,@DepartureDate,@ArrivalPlaceID,@Price,@Note,@BookingID)";
    string SQL_UPDATE = "update CustomBooking set FacilityID = @FacilityID, GuestNumber = @GuestNumber,DeparturePlaceID= @DeparturePlaceID,DepartureDate=@DepartureDate ,ArrivalPlaceID=@ArrivalPlaceID,Price=@price,Note=@Note where ID = @id";
    string SQL_DELETE = "delete from CustomBooking where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public CustomBookingDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get CustomBooking by booking id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public CustomBooking GetCustomBookingByBID(int bid)
    {
        CustomBooking cusBook = new CustomBooking();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_BID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@bId", bid);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    cusBook.ID = dr.GetInt32(0);
                    cusBook.Facility.Id = dr.GetInt32(1);
                    cusBook.GuestNumber= dr.GetInt32(2);                   
                    cusBook.DeparturePlace.Id = dr.GetInt32(3);
                    cusBook.DepartureTime = dr.GetDateTime(4);                   
                    cusBook.ArrivalPlace.Id = dr.GetInt32(5);
                    cusBook.Spot.Id = dr.GetInt32(6);
                    cusBook.Price = dr.GetDecimal(7);
                    cusBook.Note = dr.GetString(8);                    
                    cusBook.BookingID = dr.GetInt32(9);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return cusBook;
    }
/// <summary>
/// Get custom  booking by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
    public CustomBooking GetCustomBooking(int id)
    {
        CustomBooking cusBook = new CustomBooking();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    cusBook.ID = dr.GetInt32(0);
                    cusBook.Facility.Id = dr.GetInt32(1);
                    cusBook.GuestNumber = dr.GetInt32(2);
                    cusBook.DeparturePlace.Id = dr.GetInt32(3);
                    cusBook.DepartureTime = dr.GetDateTime(4);
                    cusBook.ArrivalPlace.Id = dr.GetInt32(5);
                    cusBook.Spot.Id = dr.GetInt32(6);
                    cusBook.Price = dr.GetDecimal(7);
                    cusBook.Note = dr.GetString(8);
                    cusBook.BookingID = dr.GetInt32(9);
                                 
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return cusBook;
    }
    /// <summary>
    /// Get all CustomBookings 
    /// </summary>
    /// <returns></returns>
    public IList<CustomBooking> GetAllCustomBookings()
    {
        IList<CustomBooking> list = new List<CustomBooking>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    CustomBooking cusBook = new CustomBooking();
                    cusBook.BookingID = dr.GetInt32(0);
                    cusBook.Facility.Id = dr.GetInt32(1);
                    cusBook.GuestNumber = dr.GetInt32(2);
                    cusBook.DeparturePlace.Id = dr.GetInt32(3);
                    cusBook.DepartureTime = dr.GetDateTime(4);
                    cusBook.ArrivalPlace.Id = dr.GetInt32(5);
                    cusBook.Price = dr.GetDecimal(6);
                    cusBook.Note = dr.GetString(7);                   
                    cusBook.BookingID = dr.GetInt32(8);
                    list.Add(cusBook);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Insert new CustomBooking into database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Insert(CustomBooking cusBook)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@FacilityID", cusBook.Facility.Id);
            command.Parameters.AddWithValue("@GuestNumber", cusBook.GuestNumber);
            command.Parameters.AddWithValue("@DeparturePlaceID", cusBook.DeparturePlace.Id);
            command.Parameters.AddWithValue("@DepartureDate", cusBook.DepartureTime);
            command.Parameters.AddWithValue("@ArrivalPlaceID", cusBook.ArrivalPlace.Id);
            command.Parameters.AddWithValue("@Price", cusBook.Price);
            command.Parameters.AddWithValue("@Note", cusBook.Note);           ;
            command.Parameters.AddWithValue("@BookingID", cusBook.BookingID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update CustomBooking
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Update(CustomBooking cusBook)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@FacilityID", cusBook.Facility.Id);
            command.Parameters.AddWithValue("@GuestNumber", cusBook.GuestNumber);
            command.Parameters.AddWithValue("@DeparturePlaceID", cusBook.DeparturePlace.Id);
            command.Parameters.AddWithValue("@DepartureDate", cusBook.DepartureTime);
            command.Parameters.AddWithValue("@ArrivalPlaceID", cusBook.ArrivalPlace.Id);          
            command.Parameters.AddWithValue("@Price", cusBook.Price);            
            command.Parameters.AddWithValue("@Note", cusBook.Note);
            command.Parameters.AddWithValue("@id",cusBook.ID);  
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Remove CustomBooking out of database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Delete(CustomBooking cusBook)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", cusBook.BookingID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    
    /**********Page select ***************/
    
    #endregion
}
